BEGIN;
/*
  FLATTENDED CODE HIERARCHIES
  lookup tabellen voor correcte uri's, correcte namen, children, etc.
  
*/
delete from fn.taxa_correct_loops;
insert into fn.taxa_correct_loops
select txn1.cde_id, txn1.correct_cde_id
from dictionary.taxa txn1
join dictionary.taxa txn2 on txn1.correct_cde_id=txn2.cde_id and txn2.correct_cde_id=txn1.cde_id;

delete from fn.taxa_correct;
insert into fn.taxa_correct
with recursive
correctable as
 (select cde.identity as identity, txn.cde_id as cde_id,
		case when tcl.cde_id > tcl.correct_cde_id then NULL --op deze manier blijven de soorten die naar elkaar verwijzen, behouden inlcusief 1 van de 2 correct verwijzingen.
		     else txn.correct_cde_id 
		     end as correct_cde_id, true as corrected
	from dictionary.taxa txn
	join dictionary.codes cde on txn.cde_id = cde.cde_id
	left join fn.taxa_correct_loops tcl on tcl.cde_id=txn.cde_id
	where txn.correct_cde_id notnull
union all
	select c.identity, c.cde_id,txn2.correct_cde_id,true as corrected
	--Voor andere doeleinden wil je mss liever tax2.cde_id gebruiken, maar hier zijn al alle niet-correcte taxa, dus het is niet nodig (zelfs fout) om nog weer stapjes omhoog te doen in de boom. Later (in de CTE "foo") worden alleen de correct_id's eruit gehaald die helemaal correct zijn, door een join met dictionary.taxa where correct_cde_id isnull. We willen elke niet-correcte cde_id vertalen naar de uiteindelijke correct_cde_id, daarom is cde_id steeds dezelfde, en gaat correct_cde_id steeds een verder.
	from correctable c
	join dictionary.taxa txn2 on c.correct_cde_id = txn2.cde_id
	)
,correct as
(select cde.cde_id ,cde.identity as original_identity,cde.cde_id as correct_cde_id, cde.identity as identity,false as corrected--,fn.sci(cde.cde_id) as sci_name, fn.nl(cde.cde_id) as nl_name, fn.categories('http://ndff-ecogrid.nl/categories/speciesindex',cde.cde_id) as soortgroepen, fn.policy_names(cde.cde_id) as wet_beleid 
   from dictionary.codes cde
   join dictionary.taxa on taxa.cde_id = cde.cde_id
   where taxa.correct_cde_id isnull
	)
,foo as 
(select c.cde_id,c.identity as original_identity, cde.cde_id as correct_cde_id, cde.identity as identity,c.corrected 
from correctable c
join dictionary.taxa t on c.correct_cde_id = t.cde_id
join dictionary.codes cde on c.correct_cde_id = cde.cde_id
where t.correct_cde_id isnull
union all
select * from correct
	)
select cde_id,original_identity, correct_cde_id, identity, corrected,fn.sci(correct_cde_id) as sci_name, fn.nl(correct_cde_id) as nl_name, fn.categories('http://ndff-ecogrid.nl/categories/speciesindex',correct_cde_id) as soortgroepen
, fn.policy_names(correct_cde_id) as wet_beleid, fn.hascategory(correct_cde_id,'http://ndff-ecogrid.nl/categories/policy/ffw/1') as ffwtabel1, fn.hascategory(correct_cde_id,'http://ndff-ecogrid.nl/categories/policy/ffw/2l') as ffwtabel2, fn.hascategory(correct_cde_id,'http://ndff-ecogrid.nl/categories/policy/ffw/3b') as ffwtabel3, fn.hascategory(correct_cde_id,'http://ndff-ecogrid.nl/categories/policy/rodelijst') as rodelijst, fn.hascategory(correct_cde_id,'http://ndff-ecogrid.nl/categories/policy/hrl/2') as hrlbijlage2, fn.hascategory(correct_cde_id,'http://ndff-ecogrid.nl/categories/policy/hrl/4') as hrlbijlage4
from foo ;

delete from fn.taxa_loopback;
insert into fn.taxa_loopback
select tc1.cde_id, tc2.cde_id as cde_id2
from fn.taxa_correct tc1
join fn.taxa_correct tc2 on (tc1.correct_cde_id = tc2.correct_cde_id);

delete from fn.taxa_children;
insert into fn.taxa_children
with recursive
taxr as
	(select cde_id as start_cde_id,cde_id
	from dictionary.taxa 
union all
	select taxr.start_cde_id,taxa.cde_id
	from dictionary.taxa
	join taxr on (taxa.parent_cde_id = taxr.cde_id))
select * from taxr;

COMMIT;
